




ALTER      proc sp_approve_lineItem

@lineItem_id int, @salesOrder_Id int, @user_id varchar(20),
@user_name varchar(50), @user_group varchar(50), @is_hold int

AS


declare @status varchar(15)

if(@is_hold = 0)
	set @status = 'HOLD'
else
	set @status = 'PENDING'

/*
 * Check the lineItem is scheduled or not..
 * If it is scheduled, and approved by all means then this order is approvoved
 */

declare @is_scheduled varchar

select @is_scheduled = (select cool_schedule_date from customer_open_order_lineitem
				 where lineItem_id = @lineItem_id)
print 'is scheduled'
print @is_scheduled
/*
 * Insert/Update LineItem in the LineItem_Hold_Table
 */

if(@is_hold = 0)
begin
	/*
	 * If it is hold by user then needs to update the table with thier group and userId
	 */
	if not exists (SELECT * FROM LineItem_Hold_Table WHERE LineItem_ID = @lineItem_id and 
			Hold_Type = @user_group)
	begin
		INSERT INTO LineItem_Hold_Table (LineItem_Id, SalesOrder_Id, Hold_Type, Hold_User,
			Is_Approved, TimeStamp) VALUES(@lineItem_id, @salesOrder_id, @user_group,
							@user_id,1,CURRENT_TIMESTAMP)
	end
	else
	begin
		UPDATE LineItem_Hold_Table SET Hold_User = @user_id, Is_Approved = 1
		WHERE LineItem_Id = @lineItem_id and Hold_Type = @user_group
	end
	/*
	 * Update the Customer_Open_Order_LineItem and Sales_Order Table with status
	 */
	UPDATE Customer_Open_Order_LineITem SET COOL_Status = @status
		WHERE LineItem_ID = @lineItem_id
	UPDATE Sales_Order SET SO_Status = @status,TimeStamp = CURRENT_TIMESTAMP 
		WHERE SalesOrder_Id = @salesOrder_id

end
else
begin
	/*
	 * Update the LineItem_Hold_Table as Is_Approved = 0 if scheduled date is set
	 */
	if exists (SELECT * FROM LineItem_Hold_Table WHERE LineItem_ID = @lineItem_id and 
		Hold_Type = @user_group)
	begin
	  /*
  	   * If it is operations then it needs to be scheduled
	   */
	  if((@is_scheduled is null) and (@user_group = 'OPERATIONS'))
	  begin
		print 'not schedule and not approved'
		UPDATE LineItem_Hold_Table SET Is_Approved = 1, Hold_User = @user_id
		WHERe LineItem_ID = @lineItem_id and Hold_Type = @user_group
	  end
	  else
	  begin	
		print ' schedule and approved'
		UPDATE LineItem_Hold_Table SET Is_Approved = 0, Hold_User = @user_id
		WHERe LineItem_ID = @lineItem_id and Hold_Type = @user_group
	  end
	end
	else
	begin
	   /*
	    * If it is hold by operations and schedule date is not set means it is hold by operations
	    */
  	  if((@is_scheduled is null) and (@user_group = 'OPERATIONS'))
	  begin
	print 'not schedule and insert 2'
		INSERT INTO LineItem_Hold_Table (LineItem_Id, SalesOrder_Id, Hold_Type, Hold_User,
			Is_Approved, TimeStamp) VALUES(@lineItem_id, @salesOrder_id, @user_group,
						@user_id,1,CURRENT_TIMESTAMP)
	  end
	end
	
end
/*
 * Check if the lineItem is completly approved by this approver group
 */
declare @is_lineItem_approved_by_group int

select @is_lineItem_approved_by_group = ( SELECT count(*) FROM LineItem_Hold_Table WHERE 
	SalesOrder_Id = @salesOrder_id and Hold_Type = @user_group and Is_Approved = 1)

print 'is lineitem approved by group'
print  @is_lineItem_approved_by_group

/*
 * Update the sales order table with userId ,
 * if count = 0 this lineItem is approved by this group
 */

if(@is_lineItem_approved_by_group = 0)
begin
	UPDATE Sales_Order_Hold_Table SET User_ID = @user_id, Is_Approved = 0 
		WHERE Hold_Type = @user_group and SalesOrder_ID = @salesOrder_id	
end
else
begin
	UPDATE Sales_Order_Hold_Table SET User_ID = @user_id, Is_Approved = 1
		WHERE Hold_Type = @user_group and SalesOrder_ID = @salesOrder_id
end



/*
 * Check if this lineItem is completly approved by all approvers
 */

declare @is_lineItem_approved_by_all int

SELECT @is_lineItem_approved_by_all = ( SELECT count(*) FROM LineItem_Hold_Table WHERE
		LineItem_Id = @lineItem_id and Is_Approved = 1)
print 'is lineitem approved by all'
print @is_lineItem_approved_by_all
/*
 * execute the cancel order proc if it is already cancelled(this is approved by all)
 */
declare @is_cancelled_lineItem varchar(50)

if (@is_lineItem_approved_by_all = 0)
begin
	select @is_cancelled_lineItem = (SELECT LineItem_ID FROM
	Customer_Open_Order_LineItem WHERE LineItem_ID = @lineItem_ID and 
	COOL_Is_Cancel = 0)
	print 'is cancelled'
	print  @is_cancelled_lineItem
	if(@is_cancelled_lineItem is not null)
	begin
		exec cancel_order_proc @lineItem_id, @salesOrder_id, @user_id, @user_name
	end
end


/*
 * Update the lineItem STATUS
 */

if (@is_lineItem_approved_by_all = 0)
begin
	print 'schedueld ??'
	print @is_scheduled
	if(@is_scheduled is not null)
	begin
		UPDATE Customer_Open_Order_LineItem SET COOL_Status = 'APPROVED',TimeStamp = CURRENT_TIMESTAMP
			 WHERE LineItem_ID = @lineItem_id		
	print ' approved' 
	
	end
	else
	begin
		UPDATE Customer_Open_Order_LineItem SET COOL_Status = @status,TimeStamp = CURRENT_TIMESTAMP
			WHERE LineItem_ID = @lineItem_id
		UPDATE Sales_Order_Hold_Table SET Is_Approved = 1
			WHERE SalesOrder_Id = @salesOrder_id and Hold_Type='OPERATIONS'
		print 'not approved'
		print @status
	end	
	
end





/* 
 * Check if the all lineItems in the order is scheduled by all the operations
 * Operations approval is by lineItem and other approvers is by order
 */

declare @is_order_approved int 

/*
 * Check if the order is approved by all Approvers
 */
select @is_order_approved = ( SELECT count(*) FROM Sales_Order_Hold_Table
	WHERE SalesOrder_ID = @salesOrder_id
	and Is_Approved = 1)


/*
 * if count = 0 then it is approved by other three approvers
 */

if ( @is_order_approved = 0)
begin

	/*
	 * Update the sales_order table for is_pending 
 	 */
	UPDATE Sales_Order SET SO_Is_Pending = 1, SO_Status = 'APPROVED', TimeStamp = CURRENT_TIMESTAMP 
		WHERE SalesOrder_ID = @salesOrder_id
	
end
else
begin
	UPDATE Sales_Order SET SO_Is_Pending = 0, SO_Status = @status,TimeStamp = CURRENT_TIMESTAMP
		WHERE SalesOrder_ID = @salesOrder_id
end













